﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class quanlymathang_DAO
    {

        //public List<quanlymathang_DTO > LayDanhSachMH()
        //{
        //    string sql = "SELECT * FROM MATHANG";

        //    OleDbConnection conn;
        //    conn = DataProvider.ConnectDB();
        //    OleDbCommand cmd = new OleDbCommand(sql, conn);
        //    OleDbDataReader reader = cmd.ExecuteReader();

        //    List<quanlymathang_DTO > danhsach = new List<quanlymathang_DTO >();
        //    while (reader.Read())
        //    {
        //        quanlymathang_DTO  qlmtDTO = new quanlymathang_DTO();
        //        qlmtDTO.mamathang = reader.GetInt32(0);
        //        qlmtDTO.tenhang  = reader.GetString(1);
        //        qlmtDTO.xuatxu = reader.GetInt32(2);
        //        qlmtDTO.donvitinh  = reader.GetString(3);
        //        qlmtDTO.soluongton = reader.GetInt32(4);
        //        qlmtDTO.gianhap = reader.GetInt32(5);
        //        qlmtDTO.tinhtrang = reader.GetString(6);
        //        //qlmtDTO.tinhtrang = reader.GetInt32(6);
        //        qlmtDTO.ghichu = reader.GetString(7);
        //        qlmtDTO.dongia = reader.GetInt32(8);
        //        qlmtDTO.idlloai = reader.GetInt32(9);
        //        danhsach.Add(qlmtDTO);
        //    }
        //    conn.Close();
        //    return danhsach;
        //}


        public List<quanlymathang_DTO> LayDanhSachMH1()
        {
            string sql = "SELECT * FROM MATHANG";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<quanlymathang_DTO> danhsach = new List<quanlymathang_DTO>();
            while (reader.Read())
            {
                quanlymathang_DTO qlmtDTO = new quanlymathang_DTO();
                qlmtDTO.mamathang = reader.GetInt32(0);
                qlmtDTO.tenhang = reader.GetString(1);
                qlmtDTO.xuatxu = reader.GetInt32(2);
                qlmtDTO.donvitinh = reader.GetString(3);
                qlmtDTO.soluongton = reader.GetInt32(4);
                qlmtDTO.gianhap = reader.GetInt32(5);
                qlmtDTO.tinhtrang = reader.GetString(6);
                //qlmtDTO.tinhtrang = reader.GetInt32(6);
                qlmtDTO.ghichu = reader.GetString(7);
                qlmtDTO.dongia = reader.GetInt32(8);
                qlmtDTO.idlloai = reader.GetInt32(9);
                danhsach.Add(qlmtDTO);
            }
            conn.Close();
            return danhsach;
        }


        


        public int ThemMatHang(quanlymathang_DTO mhDTO)
        {
            string sql = "INSERT INTO MATHANG(TenHang,XuatXu,DVTinh,SoLuongTon,GiaNhap,TinhTrang,GhiChu,DonGia,IDLoai) VALUES(@TenHang,@XuatXu,@DVTinh,@SoLuongTon,@GiaNhap,@TinhTrang,@GhiChu,@DonGia,@IDLoai)";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
            para.Value = mhDTO.tenhang ;
            para = cmd.Parameters.Add("@XuatXu", OleDbType.VarChar);
            para.Value = mhDTO.xuatxu ;
            para = cmd.Parameters.Add("@DVTinh", OleDbType.VarChar);
            para.Value = mhDTO.donvitinh ;
            para = cmd.Parameters.Add("@SoLuongTon", OleDbType.VarChar);
            para.Value = mhDTO.soluongton;
            para = cmd.Parameters.Add("@GiaNhap", OleDbType.VarChar);
            para.Value = mhDTO.gianhap;
            para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);
            para.Value = mhDTO.tinhtrang;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = mhDTO.ghichu;
            para = cmd.Parameters.Add("@DonGia", OleDbType.VarChar);
            para.Value = mhDTO.dongia ;
            para = cmd.Parameters.Add("@IDLoai", OleDbType.VarChar);
            para.Value = mhDTO.idlloai ;
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }

        public int XoaMatHang(string mamh)
        {
            
            string sql = "DELETE FROM MATHANG WHERE MaMatHang=" + mamh + "";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }

        public int CapNhatMatHang(quanlymathang_DTO cnDTO)
        {
            string sql = "UPDATE MATHANG SET TenHang=@TenHang,XuatXu=@XuatXu,DVTinh=@DVTinh,SoLuongTon=@SoLuongTon,GiaNhap=@GiaNhap,TinhTrang=@TinhTrang,GhiChu=@GhiChu, DonGia = @DonGia,IDLoai = @IDLoai  WHERE MaMatHang=@MaMatHang";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
            para.Value = cnDTO.tenhang;
            para = cmd.Parameters.Add("@XuatXu", OleDbType.VarChar);
            para.Value = cnDTO.xuatxu;
            para = cmd.Parameters.Add("@DVTinh", OleDbType.VarChar);
            para.Value = cnDTO.donvitinh;
            para = cmd.Parameters.Add("@SoLuongTon", OleDbType.VarChar);
            para.Value = cnDTO.soluongton;
            para = cmd.Parameters.Add("@GiaNhap", OleDbType.VarChar);
            para.Value = cnDTO.gianhap;
            para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);
            para.Value = cnDTO.tinhtrang;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.ghichu;
            para = cmd.Parameters.Add("@DonGia", OleDbType.VarChar);
            para.Value = cnDTO.dongia ;
            para = cmd.Parameters.Add("@IDLoai", OleDbType.VarChar);
            para.Value = cnDTO.idlloai;
            //  para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);

            para = cmd.Parameters.Add("@MaMatHang", OleDbType.VarChar);
            para.Value = cnDTO.mamathang;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }

    }
}
